﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using BO;
using UIControls;
using UIControls.Grid;
using UI.Core;
namespace UI.EditForm
{
   
    public partial class DesignReportForm :EditFormBase,IMenuForm
    {
        
        public class ReportField
        {
            public string TableName { get; set; }
            public string FieldName { get; set; }
            public string Field { get { return string.Format("{0}_{1}", TableName, FieldName); } }

            public string TableDesc { get; set; }
            public string FieldDesc { get; set; }
            public string Desc { get { return string.Format("{0}_{1}",TableDesc,FieldDesc); } }

            public string SelectSql { get { return string.Format("{0}.{1} as {2}", TableName, FieldName, FieldDesc); } }

            public string GroupBySql { get { return string.Format("{0}.{1} ", TableName, FieldName); } }

        }
        List<ReportField> list = new List<ReportField>();
        

        public DesignReportForm()
        {
            InitializeComponent();
            menuPanel.Visible = false;
            menuPanel.Height = 0;
            MyGrid = grid;
            flowLayoutPanel1.Visible = false;
            flowLayoutPanel1.Height = 0;
        }

        protected override void OnLoad(EventArgs e)
        {
            dateCell.Range = DateRange.ThisMonth;
            base.OnLoad(e);
            tableBox.ValueMember = "Key";
            tableBox.DisplayMember = "Value";
            tableBox.DataSource = DescriptionHandler.GetAllTables();
            fieldGrid.Columns.Add(ColumnHelper.NewColumn("TableDesc",100,"表名"));
            fieldGrid.Columns.Add(ColumnHelper.NewColumn("FieldDesc", 100, "列名"));
         
        }
        private string curTable = "";
        private string curTableName = "";
        private void tableBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tableBox.SelectedIndex < 0)
                return;
            curTable = tableBox.SelectedValue.ToString();
            curTableName = tableBox.Text;
            fieldBox.DataSource = DescriptionHandler.GetAllFields(curTable);
            fieldBox.ValueMember = "Key";
            fieldBox.DisplayMember = "Value";
        }

        private void fieldBox_DoubleClick(object sender, EventArgs e)
        {
            if (fieldBox.SelectedIndex < 0)
                return;
            var p=fieldBox.SelectedValue.ToString();
            var d= fieldBox.Text;

            var exist = list.FirstOrDefault(item => item.TableName == curTable && item.FieldName == p);
            if (exist == null)
            {

                var rf = new ReportField();
                rf.TableName = curTable;
                rf.TableDesc = curTableName;

                rf.FieldName = p;
                rf.FieldDesc = d;
                list.Add(rf);
                fieldGrid.Objects = list;

            }
        }
        string GetSqlString()
        {
            if (list.Count == 0)
                return "";
            list.RemoveAll(item => item.FieldName == DataBaseObject.Columns.Billing_Amount && item.TableName == DataBaseObject.Tables.Billing);

            var selectStr = string.Join(",", list.Select(item => item.SelectSql));
            var groupByStr = string.Join(",", list.Select(item => item.GroupBySql));
            var sqlTxt = string.Format(
                @"Select {0},Sum(Billing.Amount) as 金额
                From Billing left join Staff on Billing.StaffName=Staff.Name And Billing.CompanyName=Staff.CompanyName
Left Join Company on Billing.CompanyName=Company.Name
left join Center on Billing.CenterName=Center.Name
Left join Department on Billing.DepartmentName=Department.Name
Left join Product on Billing.ProductName=Product.Name
left join ProductGroup on Billing.ProductGroupName=ProductGroup.Name
Where Billing.TransDate>='{1}' And Billing.TransDate<='{2}' And Billing.HasDeleted=0 And IsConfirm=1
Group By {3}
", selectStr, dateCell.BeginDate, dateCell.EndDate, groupByStr);

            return sqlTxt;
        }
        private void btnQuery_Click(object sender, EventArgs e)
        {

            var sqlTxt = GetSqlString();
            var dt = BinDataTableAdapter.GetDataTable(sqlTxt);          
            grid.SetObjects(dt);
            RowCount = dt.Rows.Count;
            var tt = 0d;
            foreach (DataRow row in dt.Rows)
            {
                var amt=0d;
                double.TryParse(row["金额"].ToString(), out amt);
                tt += amt;
            }
            TotalAmt = tt;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            var name = txtReportName.Text.Trim();
            var sql = GetSqlString().Trim();
            if (sql.Length == 0 || name.Length == 0)
            {
                MessageBox.Show("没有添加[列]或者没有[报表名称].");
            }
            else
            {
                var obj = new UserReport();
                obj.ReportName = name;
                obj.ReportSql = sql;
                obj.Save();
                MessageBox.Show("保存成功.");
                txtReportName.Text = "";
            }
        }

        private void btnRemove_Click(object sender, EventArgs e)
        {
            var obj = fieldGrid.SelectedObject as ReportField;
            if (obj != null)
            {
                list.Remove(obj);
                fieldGrid.SetObjects(list);
              
            }

        }

        private void btnUp_Click(object sender, EventArgs e)
        {
            var obj = fieldGrid.SelectedObject as ReportField;
              if (obj != null)
              {
                  var idx = list.IndexOf(obj);
                  if (idx == 0)
                      return;
                  list.Remove(obj);
                  list.Insert(idx - 1, obj);
                  fieldGrid.SetObjects(list);
                  fieldGrid.SelectObject(obj);
              }
        }

        private void btnDown_Click(object sender, EventArgs e)
        {
            var obj = fieldGrid.SelectedObject as ReportField;
            if (obj != null)
            {
                var idx = list.IndexOf(obj);
                if (idx == list.Count-1)
                    return;
                list.Remove(obj);
                list.Insert(idx + 1, obj);
                fieldGrid.SetObjects(list);
                fieldGrid.SelectObject(obj);
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            var dt = grid.GenerateDataTable();
            var excel = new ExcelAdapter();
            excel.SaveExcel(dt, "", this.Text, this.Text);
        }
    }
}
